package tgc.edu.tms.repository;

import java.util.List;

import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import tgc.edu.tms.custom.CommonRepository;
import tgc.edu.tms.entity.Profession;
@Repository
public interface ProfessionRepository extends CommonRepository<Profession, Integer> {
	@Query(value="SELECT DISTINCT year FROM profession ",nativeQuery=true)	
	List<Profession> findAllyear();
	@Query(value="SELECT DISTINCT college FROM profession",nativeQuery=true)
	List<String> findAllCollege();

	@Query(value="SELECT DISTINCT year FROM profession",nativeQuery=true)
	List<String> findAllYear();

	@Query(value="SELECT DISTINCT college FROM profession WHERE year=?1",nativeQuery=true)
	List<String> findCollegeByYear(String value);

	@Query(value="SELECT  profesion FROM profession WHERE year=?2 AND college=?1",nativeQuery=true)
	List<String> findProfessionByYearAndCollege(String college, String year);

	Profession findByYearAndCollegeAndProfesion(String year,String college,String profesion);
	
	@Query(value="SELECT  profesion FROM profession WHERE year=?1",nativeQuery=true)
	List<String> findProfesionByYear(String year);

	@Query(value="SELECT id FROM profession WHERE profesion=?1 AND year=?2",nativeQuery=true)
	Integer findIdByProfesionAndYear(String major,String years);
	@Query(value="SELECT DISTINCT year FROM profession ",nativeQuery=true)	
	List<Profession> findByprofessionyear();
	List<Profession> findByYear(String year);
	@Query(value="SELECT * FROM profession WHERE year=?1 AND profesion=?2",nativeQuery=true)
	Profession findByyearAndprofesion(String year, String profesion);

	@Query(value="SELECT * FROM profession WHERE college=?1 AND year=?2",nativeQuery=true)
	List<Profession> findByCollegeAndYear(String college,String year);
	
	@Transactional
	@Modifying
	@Query(value="INSERT INTO profession VALUES (NULL,NULL,?2,null,?1,?3,NULL)",nativeQuery=true)
	public void insert(String year, String college, String profesion);
	
	List<Profession> findByYearAndCollege(String year, String college);
	@Query(value="select * from profession where year=?1",nativeQuery=true)
	List<Profession> findYear(String year);
	
	Profession findByYearAndCollegeAndProfesionAndCode(String year, String college, String profesion, String code);
}
